﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Tp.Interfaces.Datalayer;

namespace Tp.Services.DataLayer
{
	  public class BaseSqlDataLayer : IDataLayer

	  {

			#region Members

			private IntPtr handle;
			private Component component = new Component();
			private bool disposed = false;
			private string connectionString;

			#endregion

			#region Contructor

			public BaseSqlDataLayer() { Dispose(false); }

			#endregion

			#region Initialize

			public void Initialize(string connectionString)
			{
				  this.connectionString = connectionString;
			}

			#endregion

			#region Properties

			protected string ConnectionString
			{
				  get { return this.connectionString; }
			}

			#endregion

			#region Methods

			protected void CloseConnection(SqlConnection conn)
			{
				  try
				  {
						conn.Close();
				  }
				  catch { }
			}

			public void Dispose()
			{
				  Dispose(true);
				  // This object will be cleaned up by the Dispose method.
				  // Therefore, you should call GC.SupressFinalize to
				  // take this object off the finalization queue
				  // and prevent finalization code for this object
				  // from executing a second time.
				  GC.SuppressFinalize(this);
			}

			private void Dispose(bool disposing)
			{
				  // Check to see if Dispose has already been called.
				  if (!this.disposed)
				  {
						// If disposing equals true, dispose all managed
						// and unmanaged resources.
						if (disposing)
						{
							  // Dispose managed resources.
							  component.Dispose();
						}

						// Call the appropriate methods to clean up
						// unmanaged resources here.
						// If disposing is false,
						// only the following code is executed.
						CloseHandle(handle);
						handle = IntPtr.Zero;

						// Note disposing has been done.
						disposed = true;

				  }
			}

			#endregion

			#region Functions

			protected SqlConnection OpenConnection()
			{
				  SqlConnection conn = new SqlConnection(this.ConnectionString);
				  conn.Open();
				  return conn;
			}

			#region DataReader

			protected virtual SqlDataReader GetDataReader(string sql)
			{
				  SqlCommand cmd = new SqlCommand(sql, this.OpenConnection());
				  return cmd.ExecuteReader(CommandBehavior.CloseConnection);
			}

			protected virtual SqlDataReader GetDataReader(string sql, params SqlParameter[] parameters)
			{
				  SqlCommand cmd = new SqlCommand(sql, this.OpenConnection());
				  if (parameters != null)
				  {
						foreach (SqlParameter parameter in parameters)
						{
							  if (parameter != null)
							  {
									cmd.Parameters.Add(parameter);
							  }
						}
				  }
				  return cmd.ExecuteReader(CommandBehavior.CloseConnection);
			}

			protected virtual SqlDataReader GetDataReaderFromStoredProcedure(string name, params SqlParameter[] parameters)
			{
				  SqlCommand cmd = new SqlCommand(name, this.OpenConnection());
				  cmd.CommandType = CommandType.StoredProcedure;
				  if (parameters != null)
				  {
						foreach (SqlParameter parameter in parameters)
						{
							  if (parameter != null)
							  {
									cmd.Parameters.Add(parameter);
							  }
						}
				  }
				  return cmd.ExecuteReader(CommandBehavior.CloseConnection);
			}

			#endregion

			#region DataTable

			protected virtual DataTable GetDataTable(string sql)
			{
				  return GetDataTable(sql, null, new SqlParameter[] { });
			}

			protected virtual DataTable GetDataTable(string sql, params SqlParameter[] parameters)
			{
				  return GetDataTable(sql, null, parameters);
			}

			protected virtual DataTable GetDataTable(string sql, DataTable table, params SqlParameter[] parameters)
			{
				  using (SqlConnection connection = this.OpenConnection())
				  {
						SqlCommand cmd = new SqlCommand(sql, connection);
						if (parameters != null)
						{
							  foreach (SqlParameter parameter in parameters)
							  {
									if (parameter != null)
									{
										  cmd.Parameters.Add(parameter);
									}
							  }
						}
						SqlDataAdapter adapter = new SqlDataAdapter(cmd);
						if (table == null)
						{
							  table = new DataTable();
						}
						adapter.Fill(table);
						return table;
				  }
			}

			protected virtual DataTable GetDataTableFromStoredProcedure(string name, params SqlParameter[] parameters)
			{
				  using (SqlConnection connection = this.OpenConnection())
				  {
						SqlCommand cmd = new SqlCommand(name, connection);
						cmd.CommandType = CommandType.StoredProcedure;

						if (parameters != null)
						{
							  foreach (SqlParameter parameter in parameters)
							  {
									if (parameter != null)
									{
										  cmd.Parameters.Add(parameter);
									}
							  }
						}
						SqlDataAdapter adapter = new SqlDataAdapter(cmd);
						DataTable table = new DataTable();
						adapter.Fill(table);
						return (table);
				  }
			}

			#endregion

			#region Executes

			protected virtual int ExecuteNonQuery(string sql)
			{
				  return ExecuteNonQuery(sql, null);
			}

			protected virtual int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
			{
				  using (SqlConnection connection = this.OpenConnection())
				  {
						SqlCommand cmd = new SqlCommand(sql, connection);
						if (parameters != null)
						{
							  foreach (SqlParameter parameter in parameters)
							  {
									if (parameter != null)
									{
										  cmd.Parameters.Add(parameter);
									}
							  }
						}
						return cmd.ExecuteNonQuery();
				  }
			}

			protected virtual int ExecuteNonQuery(string sql, out int identity, params SqlParameter[] parameters)
			{
				  using (SqlConnection connection = this.OpenConnection())
				  {
						SqlCommand cmd = new SqlCommand(sql, connection);
						if (parameters != null)
						{
							  foreach (SqlParameter parameter in parameters)
							  {
									if (parameter != null)
									{
										  cmd.Parameters.Add(parameter);
									}
							  }
						}
						int result = cmd.ExecuteNonQuery();
						identity = GetIdentity(connection);
						return result;
				  }
			}

			protected virtual int ExecuteStoredProcedure(string name, params SqlParameter[] parameters)
			{
				  using (SqlConnection connection = this.OpenConnection())
				  {
						SqlCommand cmd = new SqlCommand(name, connection);
						if (parameters != null)
						{
							  foreach (SqlParameter parameter in parameters)
							  {
									if (parameter != null)
									{
										  cmd.Parameters.Add(parameter);
									}
							  }
						}
						cmd.CommandType = CommandType.StoredProcedure;
						return cmd.ExecuteNonQuery();
				  }
			}

			protected virtual int ExecuteStoredProcedure(SqlConnection connection, string name, params SqlParameter[] parameters)
			{
				  SqlCommand cmd = new SqlCommand(name, connection);
				  if (parameters != null)
				  {
						foreach (SqlParameter parameter in parameters)
						{
							  if (parameter != null)
							  {
									cmd.Parameters.Add(parameter);
							  }
						}
				  }
				  cmd.CommandType = CommandType.StoredProcedure;
				  return cmd.ExecuteNonQuery();
			}

			protected virtual int ExecuteStoredProcedure(string name, out int identity, params SqlParameter[] parameters)
			{
				  using (SqlConnection connection = this.OpenConnection())
				  {
						SqlCommand cmd = new SqlCommand(name, connection);
						if (parameters != null)
						{
							  foreach (SqlParameter parameter in parameters)
							  {
									if (parameter != null)
									{
										  cmd.Parameters.Add(parameter);
									}
							  }
						}
						cmd.CommandType = CommandType.StoredProcedure;
						int result = cmd.ExecuteNonQuery();
						identity = this.GetIdentity(connection);
						return result;
				  }
			}

			#endregion

			#region Scalar

			protected virtual object GetScalar(string sql)
			{
				  using (SqlConnection connection = this.OpenConnection())
				  {
						SqlCommand cmd = new SqlCommand(sql, connection);
						return cmd.ExecuteScalar();
				  }
			}

			protected virtual object GetScalar(SqlConnection connection, string sql)
			{
				  SqlCommand cmd = new SqlCommand(sql, connection);
				  return cmd.ExecuteScalar();
			}

			protected virtual object GetScalar(string sql, params SqlParameter[] parameters)
			{
				  using (SqlConnection connection = this.OpenConnection())
				  {
						SqlCommand cmd = new SqlCommand(sql, connection);
						if (parameters != null)
						{
							  foreach (SqlParameter parameter in parameters)
							  {
									if (parameter != null)
									{
										  cmd.Parameters.Add(parameter);
									}
							  }
						}
						return cmd.ExecuteScalar();
				  }
			}

			protected virtual object GetScalar(SqlConnection connection, string sql, params SqlParameter[] parameters)
			{
				  SqlCommand cmd = new SqlCommand(sql, connection);
				  if (parameters != null)
				  {
						foreach (SqlParameter parameter in parameters)
						{
							  if (parameter != null)
							  {
									cmd.Parameters.Add(parameter);
							  }
						}
				  }
				  return cmd.ExecuteScalar();
			}

			protected virtual object GetScalarFromStoredProcedure(string name, params SqlParameter[] parameters)
			{
				  using (SqlConnection connection = this.OpenConnection())
				  {
						SqlCommand cmd = new SqlCommand(name, connection);
						if (parameters != null)
						{
							  foreach (SqlParameter parameter in parameters)
							  {
									if (parameter != null)
									{
										  cmd.Parameters.Add(parameter);
									}
							  }
						}
						cmd.CommandType = CommandType.StoredProcedure;
						return cmd.ExecuteScalar();
				  }
			}

			#endregion

			#region Basics

			protected virtual int GetIdentity(SqlConnection connection)
			{
				  SqlCommand cmd = new SqlCommand("SELECT @@Identity;", connection);
				  return Convert.ToInt32(cmd.ExecuteScalar());
			}

			protected object CheckNull(object value)
			{
				  if (value == null)
				  {
						return DBNull.Value;
				  }
				  return value;
			}

			protected object CheckNull(DateTime dt)
			{
				  if ((dt == DateTime.MinValue) || (dt == DateTime.MaxValue)) return DBNull.Value;
				  return dt;
			}

			protected object CheckNull(Guid guid)
			{
				  if (guid == Guid.Empty) return DBNull.Value;
				  return guid;
			}

			protected string GetString(IDataReader reader, string field)
			{
				  return (reader[field] as string);
			}

			protected byte GetByte(IDataReader reader, string field)
			{
				  if (reader[field] != DBNull.Value) return (byte)reader[field];
				  return 0;
			}

			protected long GetLong(IDataReader reader, string field)
			{
				  if (reader[field] != DBNull.Value) return (Int32)reader[field];
				  return 0;
			}

			protected Guid GetGuid(IDataReader reader, string field)
			{
				  if (reader[field] != DBNull.Value) return (Guid)reader[field];
				  return Guid.Empty;
			}

			protected DateTime GetDateTime(IDataReader reader, string field)
			{
				  return this.GetDateTime(reader, field, DateTime.MinValue);
			}

			protected DateTime GetDateTime(IDataReader reader, string field, DateTime defaultValue)
			{
				  if (reader[field] != DBNull.Value) return (DateTime)reader[field];
				  return defaultValue;
			}

			protected int GetInt32(IDataReader reader, string field)
			{
				  return this.GetInt32(reader, field, 0);
			}

			protected int GetInt32(IDataReader reader, string field, int defaultValue)
			{
				  if (reader[field] != DBNull.Value) return (int)reader[field];
				  return defaultValue;
			}

			protected long GetInt64(IDataReader reader, string field)
			{
				  return this.GetInt64(reader, field, 0);
			}

			protected long GetInt64(IDataReader reader, string field, int defaultValue)
			{
				  if (reader[field] != DBNull.Value) return (long)reader[field];
				  return defaultValue;
			}

			protected decimal GetDecimal(IDataReader reader, string field)
			{
				  return this.GetDecimal(reader, field, 0);
			}

			protected decimal GetDecimal(IDataReader reader, string field, long defaultValue)
			{
				  if (reader[field] != DBNull.Value) return (decimal)reader[field];
				  return defaultValue;
			}

			protected bool GetBoolean(IDataReader reader, string field)
			{
				  return this.GetBoolean(reader, field, false);
			}

			protected bool GetBoolean(IDataReader reader, string field, bool defaultValue)
			{
				  if (reader[field] != DBNull.Value) return (bool)reader[field];
				  return defaultValue;
			}

			protected DataRelation CreateDataRelation(string relationName, DataSet ds, string parentColumn, string childColumn)
			{
				  string[] parent = parentColumn.Split('.');
				  if (parent.Length != 2)
				  {
						throw new ArgumentException("parentColumn must contain valid column name: \"table.colum\"", "parentColumn");
				  }

				  string[] child = childColumn.Split('.');
				  if (child.Length != 2)
				  {
						throw new ArgumentException("childColumn must contain valid column name: \"table.colum\"", "childColumn");
				  }

				  return new DataRelation(relationName, ds.Tables[parent[0]].Columns[parent[1]], ds.Tables[child[0]].Columns[child[1]]);
			}

			protected int SaveStream(SqlConnection connection, string column, byte[] pointer, Stream stream)
			{
				  int bufferSize = 1024;
				  SqlCommand cmd = new SqlCommand(string.Format("UPDATETEXT {0} @Pointer @Offset 0 @Bytes", column), connection);

				  SqlParameter paramPointer = cmd.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
				  paramPointer.Value = pointer;

				  SqlParameter paramData = cmd.Parameters.Add("@Bytes", SqlDbType.Image, bufferSize);

				  SqlParameter paramOffset = cmd.Parameters.Add("@Offset", SqlDbType.Int);
				  paramOffset.Value = 0;

				  BinaryReader br = new BinaryReader(stream);
				  byte[] buffer = br.ReadBytes(bufferSize);
				  int offset = 0;

				  while (buffer.Length > 0)
				  {
						paramData.Value = buffer;
						cmd.ExecuteNonQuery();
						offset += buffer.Length;
						paramOffset.Value = offset;
						buffer = br.ReadBytes(bufferSize);
				  }
				  return offset;
			}

			protected void MergeAndUpdate(DataTable newData, string sql, params SqlParameter[] parameters)
			{
				  using (SqlConnection connection = this.OpenConnection())
				  {
						SqlCommand cmd = new SqlCommand(sql, connection);
						if (parameters != null)
						{
							  foreach (SqlParameter parameter in parameters)
							  {
									if (parameter != null)
									{
										  cmd.Parameters.Add(parameter);
									}
							  }
						}
						SqlDataAdapter adapter = new SqlDataAdapter(cmd);
						DataSet ds = new DataSet();
						adapter.Fill(ds, newData.TableName);

						SqlCommandBuilder cb = new SqlCommandBuilder(adapter);
						ds.Merge(newData, false, MissingSchemaAction.Ignore);
						adapter.Update(ds.Tables[0]);
				  }
			}

			#endregion

			#endregion

			#region DLL Calls

			[System.Runtime.InteropServices.DllImport("Kernel32")]
			private extern static Boolean CloseHandle(IntPtr handle);

			#endregion
	  }
}
